﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[vwWH_ServerRoleMembers_dbo_sysadmin_Detail]
#-- Purpose:		Reports on the db_owner and sysadmin role membership grants
#--	Last Update:	01/04/2016
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE VIEW [dbo].[vwWH_ServerRoleMembers_dbo_sysadmin_Detail]
AS
SELECT		ssd.[ssd_sqlserver_name_ro],
			CAST(CASE WHEN srm.[srm_databasename] IS NULL THEN 1 ELSE 0 END as bit) [is_server_role],
			srm.[srm_databasename],
			[srm_role_name],
			[srm_member_name],
			[srm_member_type],
			[srm_member_disabled],
			[srm_start_date],
			[srm_end_date],
			arm.[arm_reasoning],
			CAST(CASE	WHEN srm.[srm_member_disabled] = 0
								AND (
									arm.[arm_id] IS NULL
									OR (
										srm.[srm_member_name] = 'sa'
										AND arm.[arm_server_ssd_id] IS NULL
									)
								)
							THEN 0
						ELSE 1
						END as bit) [satisfactory_reason]
FROM		[dbo].[tblWH_ServerRoleMembers] srm
JOIN		[dbo].[tblMSX_server_discovery] ssd ON srm.[srm_server_ssd_id] = ssd.[ssd_id]
			AND ssd.[ssd_is_disabled] = 0
LEFT JOIN	[dbo].[tblWH_ApprovedRoleMembers] armsa ON srm.[srm_server_ssd_id] = armsa.[arm_server_ssd_id]
			AND armsa.[arm_role_name] = 'sysadmin'
			AND armsa.[arm_member_name] = 'sa'
LEFT JOIN	[dbo].[tblWH_ApprovedRoleMembers] arm ON srm.[srm_role_name] = arm.[arm_role_name]
			AND srm.[srm_member_type] = arm.[arm_member_type]
			AND srm.[srm_member_name] LIKE arm.[arm_member_name]
			AND (
				arm.[arm_databasename] IS NULL
				OR srm.[srm_databasename] LIKE arm.[arm_databasename]
			)
			AND (
				(
					arm.[arm_server_ssd_id] IS NULL
					AND (
						armsa.[arm_server_ssd_id] IS NULL
						OR arm.[arm_member_name] <> 'sa'
					)
				)
				OR srm.[srm_server_ssd_id] = arm.[arm_server_ssd_id]
			)
WHERE		srm.[srm_role_name] IN ('db_owner', 'sysadmin')
			AND srm.[srm_end_date] IS NULL
